﻿CREATE PROCEDURE [maint].[CheckDevice]
@DeviceName [sysname], @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + ''''
		END

	DECLARE @RestoreSql nvarchar(256)
	SET @RestoreSql = N'RESTORE HEADERONLY FROM [' + @DeviceName + '] WITH NOUNLOAD'

	CREATE TABLE #RESTORE_OUTPUT (
		[BackupName] nvarchar(128),
		[BackupDescription] nvarchar(255),
		[BackupType] smallint,
		[ExpirationDate] datetime,
		[Compressed] bit,
		[Position] smallint,
		[DeviceType] tinyint,
		[UserName] nvarchar(128),
		[ServerName] nvarchar(128),
		[DatabaseName] nvarchar(128),
		[DatabaseVersion] int,
		[DatabaseCreationDate] datetime,
		[BackupSize] numeric(20,0),
		[FirstLSN] numeric(25,0),
		[LastLSN]  numeric(25,0),
		[CheckpointLSN] numeric(25,0),
		[DatabaseBackupLSN] numeric(25,0),
		[BackupStartDate] datetime,
		[BackupFinishDate] datetime,
		[SortOrder] smallint,
		[CodePage] smallint,
		[UnicodeLocaleId] int,
		[UnicodeComparisonStyle] int,
		[CompatibilityLevel] tinyint,
		[SoftwareVendorId] int,
		[SoftwareVersionMajor] int,
		[SoftwareVersionMinor] int,
		[SoftwareVersionBuild] int,
		[MachineName] nvarchar(128),
		[Flags] int,
		[BindingID] uniqueidentifier,
		[RecoveryForkID] uniqueidentifier,
		[Collation] nvarchar(128),
		[FamilyGUID] uniqueidentifier,
		[HasBulkLoggedData] bit,
		[IsSnapshot] bit,
		[IsReadOnly] bit,
		[IsSingleUser] bit,
		[HasBackupChecksums] bit,
		[IsDamaged] bit,
		[BeginsLogChain] bit,
		[HasIncompleteMetaData] bit,
		[IsForceOffline] bit,
		[IsCopyOnly] bit,
		[FirstRecoveryForkID] uniqueidentifier,
		[ForkPointLSN] numeric(25,0) NULL,
		[RecoveryModel] nvarchar(60),
		[DifferentialBaseLSN] numeric(25,0) NULL,
		[DifferentialBaseGUID] uniqueidentifier,
		[BackupTypeDescription] nvarchar(60),
		[BackupSetGUID] uniqueidentifier NULL,
		[CompressedBackupSize] bigint
	)

	INSERT #RESTORE_OUTPUT
	EXEC(@RestoreSql)
	SET @ERROR = @@ERROR
	IF(@ERROR <> 0)
		BEGIN
			RAISERROR('Failed to restore header for device %s',16,1,@DeviceName)
			GOTO ERROR_HANDLER
		END

	IF NOT EXISTS(
		SELECT *
		FROM #RESTORE_OUTPUT
		WHERE  ([BackupType] = 1)
			AND([Position] = 1)
	)
		BEGIN
			RAISERROR('Device %s does not contain a FULL database backup and is therefore in an invalid state',16,1,@DeviceName)
			GOTO ERROR_HANDLER
		END

	DROP TABLE #RESTORE_OUTPUT

	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0)
		SET @RC = 1

EXIT_PROC:
	IF (@RC = 0)
	AND(@OutputDiagnostics = 1)
		PRINT 'Device contains a FULL database backup and is in a valid state'

	RETURN(@RC)	
END